﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using KM.ENT;
using KM.Models;

namespace KM.DAT
{
    public class datSanPham
    {
        public datSanPham()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        SqlCommand cmd;
        SqlDataReader dr;
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    #region  === tạo mới và sửa SanPham===
    public void createEdit(entSanPham entSP)
    {
        SqlConnection sqlConnection = new SqlConnection(constr);
        sqlConnection.Open();
        cmd = new SqlCommand("sp_SanPhamCreateEdit", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] sqla = new SqlParameter[19];
        sqla[0] = new SqlParameter("Id", DbType.Int32);
		sqla[1] = new SqlParameter("TenSanPham", DbType.Int32);
		sqla[2] = new SqlParameter("MoTa", DbType.Int32);
		sqla[3] = new SqlParameter("NoiDung", DbType.Int32);
		sqla[4] = new SqlParameter("GiaBan", DbType.Int32);
		sqla[5] = new SqlParameter("GiaKM", DbType.Int32);
		sqla[6] = new SqlParameter("ThuTu", DbType.Int32);
		sqla[7] = new SqlParameter("Image", DbType.Int32);
		sqla[8] = new SqlParameter("Image1", DbType.Int32);
		sqla[9] = new SqlParameter("Image2", DbType.Int32);
		sqla[10] = new SqlParameter("Image3", DbType.Int32);
		sqla[11] = new SqlParameter("Image4", DbType.Int32);
		sqla[12] = new SqlParameter("Keywords", DbType.Int32);
		sqla[13] = new SqlParameter("Description", DbType.Int32);
		sqla[14] = new SqlParameter("Title", DbType.Int32);
		sqla[15] = new SqlParameter("NguoiTao", DbType.Int32);
		sqla[16] = new SqlParameter("NguoiSua", DbType.Int32);
		sqla[17] = new SqlParameter("TrangThai", DbType.Int32);
		sqla[18] = new SqlParameter("LoaiSanPhamId", DbType.Int32);
		
		sqla[0].Value = entSP.Id;
		sqla[1].Value = entSP.TenSanPham;
		sqla[2].Value = entSP.MoTa;
		sqla[3].Value = entSP.NoiDung;
		sqla[4].Value = entSP.GiaBan;
		sqla[5].Value = entSP.GiaKM;
		sqla[6].Value = entSP.ThuTu;
		sqla[7].Value = entSP.Image;
		sqla[8].Value = entSP.Image1;
		sqla[9].Value = entSP.Image2;
		sqla[10].Value = entSP.Image3;
		sqla[11].Value = entSP.Image4;
		sqla[12].Value = entSP.Keywords;
		sqla[13].Value = entSP.Description;
		sqla[14].Value = entSP.Title;
		sqla[15].Value = entSP.NguoiTao;
		sqla[16].Value = entSP.NguoiSua;
		sqla[17].Value = entSP.TrangThai;
		sqla[18].Value = entSP.LoaiSanPhamId;
		
        
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }
    #endregion
    #region  === getSlide SanPham===
    public List<entSanPham> getSlide()
    {
        SqlConnection sqlConnection = new SqlConnection(constr);
        sqlConnection.Open();
        cmd = new SqlCommand("sp_SanPhamGetAllSlide", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
      

        dr = cmd.ExecuteReader();
        entSanPham entSP = new entSanPham();
        List<entSanPham> lst = new List<entSanPham>();
        while (dr.Read())
        {
            entSP = new entSanPham();
            entSP.Id = Convert.ToInt32(dr["Id"].ToString());
            entSP.TenSanPham = dr["TenSanPham"].ToString();
            entSP.MoTa = dr["MoTa"].ToString();
            entSP.NoiDung = dr["NoiDung"].ToString();
            entSP.GiaBan = Convert.ToInt32(dr["GiaBan"].ToString());
            entSP.GiaKM = Convert.ToInt32(dr["GiaKM"].ToString());
            entSP.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
            entSP.Image = dr["Image"].ToString();
            entSP.Image1 = dr["Image1"].ToString();
            entSP.Image2 = dr["Image2"].ToString();
            entSP.Image3 = dr["Image3"].ToString();
            entSP.Image4 = dr["Image4"].ToString();
            entSP.Keywords = dr["Keywords"].ToString();
            entSP.Description = dr["Description"].ToString();
            entSP.Title = dr["Title"].ToString();
            entSP.NguoiTao = Convert.ToInt32(dr["NguoiTao"].ToString());
            entSP.NgayTao = dr["NgayTao"].ToString();
            entSP.NguoiSua = Convert.ToInt32(dr["NguoiSua"].ToString());
            entSP.NgaySua = dr["NgaySua"].ToString();
            entSP.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
            entSP.LoaiSanPhamId = Convert.ToInt32(dr["LoaiSanPhamId"].ToString());

            lst.Add(entSP);
        }
        dr.Close();
       

        sqlConnection.Close();
        return lst;
    }
    #endregion
        #region  === getall SanPham===
    public List<entSanPham> getAll(int Id, ref PagingInfoModel page)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SanPhamGetAllByLoaiSanPhamId", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("Id", Id);
            cmd.Parameters.AddWithValue("@PageIndex", page.CurrentPage);
            cmd.Parameters.AddWithValue("@PageSize", page.recordPerPage);
            cmd.Parameters.AddWithValue("@RecordCount", page.totalRecords).Direction = ParameterDirection.Output;

            dr = cmd.ExecuteReader(); 
            entSanPham entSP = new entSanPham();
            List<entSanPham> lst = new List<entSanPham>();
            while (dr.Read())
            {
                entSP = new entSanPham();
                entSP.Id = Convert.ToInt32(dr["Id"].ToString());
                entSP.TenSanPham = dr["TenSanPham"].ToString();
                entSP.MoTa = dr["MoTa"].ToString();
                entSP.NoiDung = dr["NoiDung"].ToString();
                entSP.GiaBan = Convert.ToInt32(dr["GiaBan"].ToString());
                entSP.GiaKM = Convert.ToInt32(dr["GiaKM"].ToString());
                entSP.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entSP.Image = dr["Image"].ToString();
                entSP.Image1 = dr["Image1"].ToString();
                entSP.Image2 = dr["Image2"].ToString();
                entSP.Image3 = dr["Image3"].ToString();
                entSP.Image4 = dr["Image4"].ToString();
                entSP.Keywords = dr["Keywords"].ToString();
                entSP.Description = dr["Description"].ToString();
                entSP.Title = dr["Title"].ToString();
                entSP.NguoiTao = Convert.ToInt32(dr["NguoiTao"].ToString());
                entSP.NgayTao = dr["NgayTao"].ToString();
                entSP.NguoiSua = Convert.ToInt32(dr["NguoiSua"].ToString());
                entSP.NgaySua = dr["NgaySua"].ToString();
                entSP.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entSP.LoaiSanPhamId = Convert.ToInt32(dr["LoaiSanPhamId"].ToString());

                lst.Add(entSP);
            }
            dr.Close();
            if (lst.Count > 0)
            {
               // int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
                page.totalRecords = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
            }
           
            sqlConnection.Close();
            return lst;
        }
        #endregion
    #region  === getall  SanPham cung laoi=======
    public List<entSanPham> getSpCungLoai(int Id)
    {
        SqlConnection sqlConnection = new SqlConnection(constr);
        sqlConnection.Open();
        cmd = new SqlCommand("sp_SanPhamGetSPCungLoai", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("Id", Id);
        dr = cmd.ExecuteReader();
        entSanPham entSP = new entSanPham();
        List<entSanPham> lst = new List<entSanPham>();
        while (dr.Read())
        {
            entSP = new entSanPham();
            entSP.Id = Convert.ToInt32(dr["Id"].ToString());
            entSP.TenSanPham = dr["TenSanPham"].ToString();
            entSP.MoTa = dr["MoTa"].ToString();
            entSP.NoiDung = dr["NoiDung"].ToString();
            entSP.GiaBan = Convert.ToInt32(dr["GiaBan"].ToString());
            entSP.GiaKM = Convert.ToInt32(dr["GiaKM"].ToString());
            entSP.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
            entSP.Image = dr["Image"].ToString();
            entSP.Image1 = dr["Image1"].ToString();
            entSP.Image2 = dr["Image2"].ToString();
            entSP.Image3 = dr["Image3"].ToString();
            entSP.Image4 = dr["Image4"].ToString();
            entSP.Keywords = dr["Keywords"].ToString();
            entSP.Description = dr["Description"].ToString();
            entSP.Title = dr["Title"].ToString();
            entSP.NguoiTao = Convert.ToInt32(dr["NguoiTao"].ToString());
            entSP.NgayTao = dr["NgayTao"].ToString();
            entSP.NguoiSua = Convert.ToInt32(dr["NguoiSua"].ToString());
            entSP.NgaySua = dr["NgaySua"].ToString();
            entSP.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
            entSP.LoaiSanPhamId = Convert.ToInt32(dr["LoaiSanPhamId"].ToString());

            lst.Add(entSP);
        }
        dr.Close();

        sqlConnection.Close();
        return lst;
    }
    #endregion
    #region  === getall  SanPham by type san pham(sanpham moi, san pham ban chaY)===
    public List<entSanPham> getListByType(int type, int top)
    {
        SqlConnection sqlConnection = new SqlConnection(constr);
        sqlConnection.Open();
        cmd = new SqlCommand("sp_SanPhamGetAllByType", sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Type", type);
        cmd.Parameters.AddWithValue("TOP", top);
        dr = cmd.ExecuteReader();
        entSanPham entSP = new entSanPham();
        List<entSanPham> lst = new List<entSanPham>();
        while (dr.Read())
        {
            entSP = new entSanPham();
            entSP.Id = Convert.ToInt32(dr["Id"].ToString());
            entSP.TenSanPham = dr["TenSanPham"].ToString();
            entSP.MoTa = dr["MoTa"].ToString();
            entSP.NoiDung = dr["NoiDung"].ToString();
            entSP.GiaBan = Convert.ToInt32(dr["GiaBan"].ToString());
            entSP.GiaKM = Convert.ToInt32(dr["GiaKM"].ToString());
            entSP.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
            entSP.Image = dr["Image"].ToString();
            entSP.Image1 = dr["Image1"].ToString();
            entSP.Image2 = dr["Image2"].ToString();
            entSP.Image3 = dr["Image3"].ToString();
            entSP.Image4 = dr["Image4"].ToString();
            entSP.Keywords = dr["Keywords"].ToString();
            entSP.Description = dr["Description"].ToString();
            entSP.Title = dr["Title"].ToString();
            entSP.NguoiTao = Convert.ToInt32(dr["NguoiTao"].ToString());
            entSP.NgayTao = dr["NgayTao"].ToString();
            entSP.NguoiSua = Convert.ToInt32(dr["NguoiSua"].ToString());
            entSP.NgaySua = dr["NgaySua"].ToString();
            entSP.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
            entSP.LoaiSanPhamId = Convert.ToInt32(dr["LoaiSanPhamId"].ToString());

            lst.Add(entSP);
        }
        dr.Close();

        sqlConnection.Close();
        return lst;
    }
    #endregion

        #region  === getinfo SanPham===
        public entSanPham getInfo(int Id)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SanPhamGetInfo", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("Id", DbType.Int32);
            sqla[0].Value = Id;

            cmd.Parameters.AddRange(sqla);
            dr = cmd.ExecuteReader();
            entSanPham entSP = new entSanPham();
            if (dr.Read())
            {
                entSP.Id = Convert.ToInt32(dr["Id"].ToString());
                entSP.TenSanPham = dr["TenSanPham"].ToString();
                entSP.MoTa = dr["MoTa"].ToString();
                entSP.NoiDung = dr["NoiDung"].ToString();
                entSP.GiaBan = Convert.ToInt32(dr["GiaBan"].ToString());
                entSP.GiaKM = Convert.ToInt32(dr["GiaKM"].ToString());
                entSP.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entSP.Image = dr["Image"].ToString();
                entSP.Image1 = dr["Image1"].ToString();
                entSP.Image2 = dr["Image2"].ToString();
                entSP.Image3 = dr["Image3"].ToString();
                entSP.Image4 = dr["Image4"].ToString();
                entSP.Keywords = dr["Keywords"].ToString();
                entSP.Description = dr["Description"].ToString();
                entSP.Title = dr["Title"].ToString();
                entSP.NguoiTao = Convert.ToInt32(dr["NguoiTao"].ToString());
                entSP.NgayTao = dr["NgayTao"].ToString();
                entSP.NguoiSua = Convert.ToInt32(dr["NguoiSua"].ToString());
                entSP.NgaySua = dr["NgaySua"].ToString();
                entSP.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entSP.LoaiSanPhamId = Convert.ToInt32(dr["LoaiSanPhamId"].ToString());

            }
            dr.Close();
            sqlConnection.Close();
            return entSP;
        }
        #endregion

        #region === xoa SanPham===
        public void delete(int id)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SanPhamDeleteById", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("Id", DbType.Int32);
            sqla[0].Value = id;

            cmd.Parameters.AddRange(sqla);
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }
        #endregion
        #region === xoa slide SanPham===
        public void deleteSlide(int id)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SanPhamDeleteSlideById", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("Id", DbType.Int32);
            sqla[0].Value = id;

            cmd.Parameters.AddRange(sqla);
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }
        #endregion
    }

}